using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace QLHXNK.DataAccess
{
    class ImportNoteDetailData
    {

        #region---------------------------------------------Thêm, xoá, sửa-----------------------------------------

        public static int CreateImportNoteDetail(int importNoteID, int itemID, int quantity, double fobUSD, double fobEUR, double cifUSD, double cifEUR, double KRfee, double HKfee, double ITALYfee, double TLfee, double otherCountryfee, double UTfee, double VATfee, double TAXfee, double otherfee, double NET1, double NET2, double sellPrice, double profit1, double profit2)
        {
            try
            {
                string sqlstring = "insert into Import_Note_Detail(import_note_id,item_id,quantity,fob_usd,fob_eur,cif_usd,cif_eur,kr_fee,hk_fee,ita_fee,tl_fee,other_country_fee,ut_fee,vat_fee,tax_fee,other_fee,net1,net2,selling_price,profit1,profit2) values(" + 
                                    importNoteID + "," + itemID + "," + quantity + "," + fobUSD + ","+fobEUR+ ","+cifUSD+","+cifEUR+","+KRfee+","+HKfee+","+ITALYfee+","+TLfee+","+otherCountryfee+","+UTfee+","+VATfee+","+TAXfee+","+otherfee+","+NET1+","+NET2+","+sellPrice+","+profit1+","+profit2+")";
                ConnectionData connectionData = new ConnectionData();
                if (connectionData.Command(sqlstring) != 0)
                    return Int32.Parse(GetLastImportNoteDetail().Rows[0]["ID"].ToString());
                return 0;
            }
            catch
            {
                return 0;
            }
        }


        public static int DeleteImportNoteDetail(int importNoteDetailID)
        {
            try
            {
                string sqlstring = "delete from Import_Note_Detail where ID=" + importNoteDetailID + "";
                ConnectionData connectionData = new ConnectionData();
                return connectionData.Command(sqlstring);
            }
            catch
            {
                return 0;
            }
        }


        public static int UpdateImportNoteDetail(int importNoteDetailID, int itemID, int quantity, double fobUSD, double fobEUR, double cifUSD, double cifEUR, double KRfee, double HKfee, double ITALYfee, double TLfee, double otherCountryfee, double UTfee, double VATfee, double TAXfee, double otherfee, double NET1, double NET2, double sellPrice, double profit1, double profit2)
        {
            try
            {
                string sqlstring = "update Import_Note_Detail set item_id=" + itemID + ",quantity=" + quantity + ",fob_usd=" + fobUSD + ",fob_eur=" + fobEUR + ",cif_usd=" + cifUSD + ", cif_eur=" + cifEUR + ", kr_fee=" + KRfee + ", hk_fee=" + HKfee + ", ita_fee=" + ITALYfee + ", tl_fee=" + TLfee + ",other_country_fee=" + otherCountryfee + ", ut_fee=" + UTfee + ", vat_fee=" + VATfee + ", tax_fee=" + TAXfee + ", other_fee=" + otherfee + ", net1=" + NET1 + ", net2=" + NET2 + ", selling_price=" + sellPrice + ", profit1=" + profit1 + ", profit2="+profit2+" where ID=" + importNoteDetailID + "";
                ConnectionData connectionData = new ConnectionData();
                return connectionData.Command(sqlstring);
            }
            catch
            {
                return 0;
            }
        }

        #endregion


        #region-------------------------------------------Lấy thông tin -------------------------------------------

        public static DataTable GetImportNoteDetail(int importNoteID, String itemName)
        {
            try
            {
                string queryCondition = "";
                if (importNoteID != -1)
                    queryCondition += " and import_note_id = " + importNoteID + " ";
                if (itemName != "")
                    queryCondition += " and i.item_name = '" + itemName.Replace("'","''") + "' ";
                string sqlstring = "select i.item_code,i.item_name,id.ID,id.import_note_id,id.item_id,id.quantity,id.fob_usd,id.fob_eur,id.cif_usd,id.cif_eur,id.kr_fee,id.hk_fee,id.ita_fee,id.tl_fee,id.other_country_fee,id.ut_fee,id.vat_fee,id.tax_fee,id.other_fee,id.net1,id.net2,id.selling_price,id.profit1,id.profit2 from Import_Note_Detail id, Item i where id.item_id=i.ID ";
                if (queryCondition != "")
                    sqlstring +=  queryCondition;
                ConnectionData connectionData = new ConnectionData();
                return connectionData.List(sqlstring);
            }
            catch
            {
                return null;
            }
        }

        public static DataTable GetLastImportNoteDetail()
        {
            try
            {
                string sqlstring = "select * from Import_Note_Detail order by ID desc ";
                ConnectionData connectionData = new ConnectionData();
                return connectionData.List(sqlstring);
            }
            catch
            {
                return null;
            }
        }

        #endregion
    }
}
